package com.ffms.dao.impl;

import com.ffms.entity.FamilyMember;
import com.ffms.entity.Money;
import com.ffms.entity.ZhiChu;
import com.ffms.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MoneyDaoImpl {
    public List<Money> findMoneyByMid(String flag, int thisId) {
        List<Money> monies = new ArrayList<Money>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_money sh join tables.t_member tm on tm.sid = sh.mid  where sh.mid = " + thisId + "&& flag in (" + flag + ")";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String thisFlag = rs.getString("flag");
                String money = rs.getString("money");
                Date time = rs.getDate("time");
                int sid = rs.getInt("sid");

                if (thisFlag.equals("0")) {
                    thisFlag = "存钱";
                } else if (thisFlag.equals("1")) {
                    thisFlag = "取钱";
                } else if (thisFlag.equals("2")) {
                    thisFlag = "借钱";
                } else if (thisFlag.equals("3")) {
                    thisFlag = "还钱";
                }
                String name = rs.getString("name");
                Money moneyE = new Money(id, thisFlag, Integer.parseInt(money), time.toString(), new FamilyMember(sid, name));
                //  将对象放入集合
                monies.add(moneyE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return monies;
    }

    public List<Money> findMoney(String flag) {
        List<Money> monies = new ArrayList<Money>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.* from tables.t_money sh join tables.t_member tm on tm.sid = sh.mid  where  flag in (" + flag + ")";
            //3.获取执行sql的对象)
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String thisFlag = rs.getString("flag");
                String money = rs.getString("money");
                Date time = rs.getDate("time");
                int sid = rs.getInt("sid");
                if (thisFlag.equals("0")) {
                    thisFlag = "存钱";
                } else if (thisFlag.equals("1")) {
                    thisFlag = "取钱";
                } else if (thisFlag.equals("2")) {
                    thisFlag = "借钱";
                } else if (thisFlag.equals("3")) {
                    thisFlag = "还钱";
                }
                String name = rs.getString("name");
                Money moneyE = new Money(id, thisFlag, Integer.parseInt(money), time.toString(), new FamilyMember(sid, name));
                //  将对象放入集合
                monies.add(moneyE);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return monies;
    }

    public void deleteMoneyById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "delete from t_money where id=?";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {id};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Money getById(int id) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Money money = null;

        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "select tm.*,sh.*  from t_money sh join tables.t_member tm on tm.sid = sh.mid where  id =" + id;
            //3.获取执行sql的对象
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            if (rs.next()) {
                int thisId = rs.getInt("id");
                String thisFlag = rs.getString("flag");
                String thisMoney = rs.getString("money");
                Date time = rs.getDate("time");
                int sid = rs.getInt("sid");
                if (thisFlag.equals("0")) {
                    thisFlag = "存钱";
                } else if (thisFlag.equals("1")) {
                    thisFlag = "取钱";
                } else if (thisFlag.equals("2")) {
                    thisFlag = "借钱";
                } else if (thisFlag.equals("3")) {
                    thisFlag = "还钱";
                }
                String name = rs.getString("name");
                money = new Money(thisId, thisFlag, Integer.parseInt(thisMoney), time.toString(), new FamilyMember(sid, name));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
                stmt.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return money;
    }

    public int updateMoney(Money money) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "update t_money set `flag` =? ,money = ? where id=? ";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {money.getFlag(), money.getMoney(), money.getId()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    public int insertMoney(Money newMoney) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int n = 0;
        try {
            conn = JDBCUtils.getConnection();
            //2.定义sql
            String sql = "insert into t_money values (id,?,?,?,?)";
            //3.获取执行sql的对象
            pstmt = conn.prepareStatement(sql);
            Object[] prams = {newMoney.getFlag(), newMoney.getMoney(), newMoney.getTime(), newMoney.getMid()};
            for (int i = 0; i < prams.length; i++) {
                pstmt.setObject(i + 1, prams[i]);
            }
            n = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }
}
